Overview - Business problem

Rockbuster Stealth LLC is a movie rental company that used to have stores around the world. Facing stiff competition from streaming services such as Netflix and Amazon Prime, the Rockbuster Stealth management team is planning to use its existing movie licenses to launch an online video rental service in order to stay competitive.

Goal

This analysis is intended to answer a series of business key questions and derive data-driven answers for company strategy in 2020.

  1. Which movies contributed the most/least to revenue gain?
  2. What was the average rental duration for all videos?
  3. Which countries are Rockbuster customers based in?
  4. Where are customers with a high lifetime value based?

Essentially, this project aims to learn relational database management systems (RDBMS) including data structure, SQL query, data filtering and cleaning, joining tables, common table expressions (CTE) as well as visualization using Tableau.

Data

Rockbuster company and data being used are all fictious. The dataset containing information about Rockbuster’s film inventory, customers, and payments, among other things is provided by Careerfoundry and can be found here.

Process

This is SQL-based project using PostgreSQL database to analyse and essentially to answer business questions of a movie rental company. I began by drafting data dictionary that can be found here.

Entity Relationship Diagram (ERD)

Sample of data dictionary

Exploratory Data Analysis (EDA)

Rockbuster overview

For the sake of visualization, the obtained data from query is presented below.

Movies and Revenue by rating and genre

Genre categories:

PG-13 - Parents Strongly Cautioned, Some Material May Be Inappropriate for Children Under 13.
NC-17 - No One 17 and Under Admitted.
PG - Parental Guidance Suggested, Some Material May Not Be Suitable for Children.
R - Restricted, Children Under 17 Require Accompanying Parent or Adult Guardian.
G - General Audiences, All Ages Admitted.

Most/least profitable movies

The extracted data from above query is presented below.

Average rental duration

As discovered previously, the average rental duration obtained from SQL query is 4.985 days or 5 days.

Revenue by country

Top customers

Conclusion

  • Popularity
    • Sports, Sci-Fi, Animation, Drama and Comedy are top 5 popular genres.
    • PG-13 rating is the most popular movie segment.
  • Global market & sales figure
    • India, China and US are the biggest market.
    • Philippines has the highest rental order / customer, while Russia has the lowest.
  • Movies revenue
    • Fast-moving rented movies (short-rental duration) are more profitable.

Recommendations

  • Top 5 movies by genre having revenue above $4,000: Sports, Sci-Fi, Animation, Drama and Comedy. Rockbuster should indeed invest more in those genres. However, these genres don‘t seem to relate to each other, thus, market (customer rental preference) by age groups shall be captured to specifically fulfill each group preferences.
  • Movies having PG-13 rating are indeed the most profitable one. However, market for NC-17, PG and R movies seem to be promising as they share similar revenue of around $12,000, which indeed could be further exploited.
  • Consider to take a closer look on the fast-moving rented movies (ex: 3 days but rented very often).
  • Consider to add movies time variability and stay up to date as all movies are from the year of 2006.
  • India, China and US are the biggest market. Those are dense populated countries, which can be commercially exploited. Rockbuster shall invest in the marketing campaign in the big population countries. The age groups data mentioned previously could help identify the market target in each respective country.
  • Large population is indeed beneficial. However, repeated order from the same customer should be taken into account such as the case with Philippines, regardless its less population, it has the highest rental order / customer compared to others. For this purpose, such loyalty program for the top 10 customer shall be considered.

Limitations

Rockbuster and dataset used are fictious, therefore, this project is customized according to the learning goals.

Find the code for this analysis Github
Find visualization in Tableau
Connect with me! Linkedin